package edu.hawaii.myisern.model;

import java.util.ArrayList;
import java.util.List;

/**
 * Applies MyIsern specific data types to the database interface.
 * 
 * @author Randy Cox
 * 
 */
public class DatabaseResearchers {

  private final String className = "DatabaseResearchers";

  private Database db;

  /** Researcher table name for production. */
  public static final String RESEARCHER_TABLE = "RESEARCHERS";

  /** Researcher table name to use. */
  private String researcherTable = RESEARCHER_TABLE;

  /** Researcher create table sql command. */
  private String researcherCreateTableSql;

  /** Researcher drop table command. */
  private String researcherDropTableSql;

  /** Researcher select sql command. */
  private String researcherSelectSql;

  /** Researcher select update command. */
  private String researcherInsertSql;

  /** Researcher select update command. */
  private String researcherUpdateSql;

  /** Researcher delete command. */
  private String researcherDeleteSql;

  /** Researcher select count command. */
  private String researcherCountSql;

  /** Researcher clear command. */
  private String researcherClearSql;

  /** Researcher list command. */
  private String researcherListSql;

  /**
   * Constructor, initializes driver, if testing, database to test database.
   * 
   */
  public DatabaseResearchers() {
    db = new Database();
    setSqlStrings();
  }

  /**
   * Sets the sql string commands based on current table names.
   */
  public final void setSqlStrings() {

    // Researcher create table sql command.
    this.researcherCreateTableSql = db.CREATE_TABLE + this.researcherTable + " ("
        + "id VARCHAR(255) PRIMARY KEY, " + "name VARCHAR(255), " + "email VARCHAR(255), "
        + "organization VARCHAR(255), " + "picturelink VARCHAR(255), " + "bio VARCHAR(255))";

    // Researcher drop table sql command.
    this.researcherDropTableSql = db.DROP_TABLE + this.researcherTable;

    // Researcher select sql command.
    this.researcherSelectSql = db.SELECT_FROM + this.researcherTable + " WHERE id = ?";

    // Researcher select update command.
    this.researcherInsertSql = db.INSERT_INTO + this.researcherTable
        + " (id, name, email, organization, picturelink, bio)" + " VALUES (?, ?, ?, ?, ?, ?)";

    // Researcher select update command.
    this.researcherUpdateSql = db.UPDATE + this.researcherTable
        + " SET name = ?, email = ?, organization = ?, picturelink = ?, bio = ?" + " WHERE id = ?";

    // Researcher delete command.
    this.researcherDeleteSql = db.DELETE_FROM + this.researcherTable + " WHERE id = ?";

    // Researcher count command.
    this.researcherCountSql = db.SELECT_COUNT + this.researcherTable;

    // Researcher delete command.
    this.researcherClearSql = db.DELETE_FROM + this.researcherTable;

    // Researcher list command.
    this.researcherListSql = db.SELECT_FROM + this.researcherTable;

  }

  /**
   * Open and connect to database.
   */
  private void connect() {
    db.connect();
  }

  /**
   * Check and create all tables.
   */
  private void open() {
    createTables();
  }

  /**
   * Check if researcher table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableResearcher() {
    connect();
    return (db.hasTable(this.researcherTable));
  }

  /**
   * Checks if researcher table exits, if not creates it.
   */
  public void createTables() {
    String methodName = ".createTables() ";
    connect();
    if (hasTableResearcher()) {
      String msg = className + methodName + this.researcherTable + " found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.researcherCreateTableSql);
      String msg = className + methodName + this.researcherTable + " not found.  Table created.";
      Log.enter(msg);
    }
  }

  /**
   * Checks database to see if researcher with id already exists.
   * 
   * @param id of researcher record.
   * @return boolean if record exists.
   */
  public Boolean hasResearcher(String id) {
    String methodName = ".hasResearcher() ";
    connect();
    String newId = Id.build(id);
    Boolean found = (get(newId) != null);
    Log.enter(className + methodName + found.toString());
    return found;
  }

  /**
   * Retrieves researcher from database.
   * 
   * @param id of researcher to retrieve.
   * @return researcher if found, null if not found.
   */
  public MyResearcher get(String id) {
    String methodName = ".get() ";
    connect();
    open();
    String newId = Id.build(id);
    Log.enter(className + methodName + newId + " count= " + count().toString());
    List<List<String>> results = db.query(this.researcherSelectSql, newId);
    for (List<String> list : results) {
      Log.enter(className + methodName + " results = " + list.get(0));
    }
    Boolean found = false;
    if (results.size() == 1) {
      String resultId = Id.build(results.get(0).get(0));
      Log.enter(className + methodName + "resultId = " + resultId);
      Log.enter(className + methodName + "newId = " + newId);
      if (resultId.equalsIgnoreCase(newId)) {
        found = true;
      }
    }
    Log.enter(className + methodName + found.toString());
    MyResearcher researcher = null;
    if (found) {
      List<String> row = results.get(0);
      researcher = new MyResearcher();
      researcher.setName(row.get(1));
      researcher.setEmail(row.get(2));
      researcher.setOrganization(row.get(3));
      researcher.setPictureLink(row.get(4));
      researcher.setBio(row.get(5));
    }
    return researcher;
  }

  /**
   * Insert or update researcher record into table.
   * 
   * @param researcher record to save.
   * @return count of researchers updated.
   */
  public Integer save(MyResearcher researcher) {
    String methodName = ".save() ";
    assert (researcher != null);
    assert (researcher.validate());
    connect();
    open();

    // If record is already in database, update it.
    Integer count = 0;
    if (hasResearcher(researcher.getId())) {
      count = db.update(this.researcherUpdateSql, researcher.getName(), researcher.getEmail(),
          researcher.getOrganization(), researcher.getPictureLink(), researcher.getBio(),
          researcher.getId());
      Log.enter(className + methodName + count.toString() + " updated.");
    }

    // If record not in database, insert it.
    else {
      count = db.update(this.researcherInsertSql, researcher.getId(), researcher.getName(),
          researcher.getEmail(), researcher.getOrganization(), researcher.getPictureLink(),
          researcher.getBio());
      Log.enter(className + methodName + count.toString() + " inserted.");
    }
    return count;
  }

  /**
   * Delete researcher information from researcher and sub tables.
   * 
   * @param id researcher record to delete.
   * @return count of records deleted.
   */
  public Integer delete(String id) {
    assert (id != null);
    String methodName = ".delete()";
    connect();
    open();

    // Make sure id is in correct format.
    String org_id = Id.build(id);

    // If organization is already in database, delete it.
    Integer deleteCount = 0;
    if (hasResearcher(org_id)) {
      deleteCount = db.update(this.researcherDeleteSql, org_id);
      Log.enter(className + methodName + deleteCount.toString() + " deleted.");
    }
    else {
      Log.enter(className + methodName + " record not found " + org_id);
    }

    return deleteCount;
  }

  /**
   * Drop researcher table.
   */
  public void dropTables() {
    String methodName = ".dropTable()";
    connect();
    if (db.hasTable(this.researcherTable)) {
      db.execute(this.researcherDropTableSql);
      String msg = className + methodName + this.researcherTable + " found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.researcherTable + " not found.  Doing nothing.";
      Log.enter(msg);
    }
  }

  /**
   * Clear all researcher information from researcher and sub tables.
   */
  public void clear() {
    String methodName = ".clear() ";
    connect();
    open();

    // If organization is already in database, delete it.
    Integer count = db.update(this.researcherClearSql);
    Log.enter(className + methodName + count.toString() + " cleared.");
  }

  /**
   * Count of researchers in table.
   * 
   * @return count of records.
   */
  public Integer count() {
    String methodName = ".count() ";
    connect();
    open();

    // Get count of the organization records
    List<List<String>> results = db.query(this.researcherCountSql);
    Integer count = Integer.parseInt(results.get(0).get(0));
    Log.enter(className + methodName + count.toString());
    return count;
  }

  /**
   * List of researchers in table.
   * 
   * @return a list of researchers.
   */
  public List<MyResearcher> list() {
    connect();
    open();
    List<MyResearcher> list = new ArrayList<MyResearcher>();
    List<List<String>> results = db.query(this.researcherListSql);
    for (List<String> row : results) {
      MyResearcher item = new MyResearcher();
      item.setName(row.get(1));
      item.setEmail(row.get(2));
      item.setOrganization(row.get(3));
      item.setPictureLink(row.get(4).trim());
      item.setBio(row.get(5));
      list.add(item);
    }
    return list;
  }

}
